-- STANDARD SCRIPT
-- INSERT DATA IN trans_t
--
--


------------------------------------------------
-- FUNCTION TO INSERT NEW ROWS INTO TRANSACTION TABLE


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION insert_trans_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
  v_trans_config varchar(1);
BEGIN

SELECT INTO v_batch_run_date our_run_date from batch_trans_t limit 1;
SELECT INTO v_cust_id cust_id from batch_trans_t limit 1;
SELECT INTO v_trans_config trans_config from trans_config_t where cust_id = v_cust_id;

PERFORM ml_upd_batch_status_trans_new_f(v_cust_id,v_batch_run_date,1,7,'START INSERT IN trans_t');



----------------------------------------------------------------
-- STEP 1 INSERT RECORDS INTO trans_t    

RAISE NOTICE 'START INSERT INTO trans_t TABLE';
  
  INSERT INTO trans_t
  SELECT
    *
  FROM
    batch_trans_t 
  ; 

GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'DONE INSERT INTO trans_t TABLE : %', v_status;




-----------------------------------------------------------------
-- STEP 2 UPDATE medlytix_recon_flag AND medlytix_recon_date 
-- WE LOOK INTO recon_t TABLE TO SEE IF UNDETERMINED PAYMENTS (medlytix_recon_flag = 'P') SHOULD BE PUSHED TO recon_t (medlytix_recon_flag SET TO 'Y')
-- , NOT PUSHED (medlytix_recon_flag SET TO 'N') OR STILL WAITING RESOLUTION (LEFT AS VALUE 'P')


RAISE NOTICE 'START UPDATE OF trans_t TABLE';




  UPDATE trans_t
  SET
    medlytix_recon_date = (CASE WHEN trans_t.medlytix_recon_flag = 'N'                                                       THEN v_batch_run_date 
                                WHEN trans_t.medlytix_recon_flag in ('Y','P') AND a.ct_prior IS NOT NULL AND a.ct_prior >= 1 THEN v_batch_run_date 
                                WHEN trans_t.medlytix_recon_flag = 'P' AND a.ct_del IS NOT NULL AND a.ct_del >= 1            THEN v_batch_run_date
                                WHEN trans_t.medlytix_recon_flag = 'P' AND a.ct_pmt IS NOT NULL AND a.ct_pmt >= 1            THEN v_batch_run_date 
                                WHEN trans_t.medlytix_recon_flag = 'P'                                                       THEN NULL
                                ELSE                                                                                              v_batch_run_date 
                                END),
    medlytix_recon_flag = (CASE WHEN trans_t.medlytix_recon_flag = 'N'                                                       THEN 'N'
                                WHEN trans_t.medlytix_recon_flag in ('Y','P') AND a.ct_prior IS NOT NULL AND a.ct_prior >= 1 THEN 'O' 
                                WHEN trans_t.medlytix_recon_flag = 'P' AND a.ct_del IS NOT NULL AND a.ct_del >= 1            THEN 'N'
                                WHEN trans_t.medlytix_recon_flag = 'P' AND a.ct_pmt IS NOT NULL AND a.ct_pmt >= 1            THEN 'Y'
                                ELSE trans_t.medlytix_recon_flag
                                END)
 
  FROM
    (SELECT 
       cust_id,
       hid,
       acctnum,
       sum(CASE WHEN prior_ins_date IS NOT NULL OR
                  substring(upper(note) from 1 for 18) = 'INSURANCE ID PRIOR' OR
                  substring(upper(medlytix_note) from 1 for 18) = 'INSURANCE ID PRIOR'    THEN 1
             ELSE                                                                              0
             END) as ct_prior,
       sum(CASE WHEN substring(upper(note) from 1 for 12) = 'PUSH PAYMENT' OR 
                  substring(upper(medlytix_note) from 1 for 12) = 'PUSH PAYMENT'          THEN 1
             ELSE                                                                              0
             END) as ct_pmt,
       sum(CASE WHEN substring(upper(note) from 1 for 14) = 'DELETE PAYMENT' OR
                  substring(upper(medlytix_note) from 1 for 14) = 'DELETE PAYMENT'          THEN 1
             ELSE                                                                                0
             END) as ct_del

     FROM
       recon_t
     WHERE
       cust_id = v_cust_id
       AND
       data_flag = 'C'
     GROUP BY
       cust_id,hid,acctnum
     ) as a
  WHERE 
    trans_t.cust_id = v_cust_id AND trans_t.hid = a.hid AND trans_t.acctnum = a.acctnum AND trans_t.medlytix_recon_date IS NULL
  ;





-----------------------------------------------------------------
-- STEP 3 - THE UPDATE ABOVE CONCERNED ACCOUNTS WITH A "C" RECORD.  FOR CLIENTS WERE WE TAKE INTO CONSIDERATION WETHER THEY FOUND INSURANCE PRIOR
-- (trans_config = 'W' - EXAMPLE CLIENTS USING WEB INTERFACE) THEN IF THERE IS NO "C" RECORD AFTER 21 DAYS WE PUSH TO recon_t EXCEPT RECORDS NEEDING
-- INVESTIGATION (medlytix_recon_flag = 'P')
-- FOR OTHER CLIENT (trans_config = 'A') WE PUSH IMMEDIATELY EXCEPT RECORDS NEEDING INVESTIGATION


IF (v_trans_config = 'W') THEN

  UPDATE trans_t
  SET
    medlytix_recon_date = v_batch_run_date
  WHERE
    cust_id = v_cust_id AND 
    v_batch_run_date IS NULL AND
    medlytix_recon_flag <> 'P' AND
    ml_ddiff_f('d',trans_t.first_delivery_date,'now') >= 21
  ;


ELSIF (v_trans_config = 'A') THEN

  UPDATE trans_t
  SET
    medlytix_recon_date = v_batch_run_date
  WHERE
    cust_id = v_cust_id AND
    v_batch_run_date IS NULL AND
    medlytix_recon_flag <> 'P'
  ;

END IF;



RAISE NOTICE 'DONE UPDATE OF trans_t TABLE';




-- STEP 3 UPDATE batch_status_trans_t WITH THE NUMBER INSERTED 

  UPDATE batch_status_trans_t
    SET
      trans_inserted = v_status
    WHERE
      (batch_status_trans_t.last_run_date = v_batch_run_date and batch_status_trans_t.cust_id = v_cust_id);

PERFORM ml_upd_batch_status_trans_new_f(v_cust_id,v_batch_run_date,1,7,'FINISH INSERT IN trans_t');



  return v_status;
END;
$$ LANGUAGE plpgsql;
